﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.App;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System.Data;
using Kingdee.BOS.ServiceHelper;


namespace HSXMBillService
{
    [Description("核算项目报表插件")]
    [Kingdee.BOS.Util.HotUpdate]
    public class HSXMReport : SysReportBaseService
    {
        #region 临时表定义
        //临时表集合
        private List<string> tempTable = new List<string>();
        //第一个临时表
        private string T_Temp_First = string.Empty;

        //最终数据表
        private string T_Temp_Last = string.Empty;
        //自己创建的临时表的SQL语句 List
        List<SqlObject> listTableSqlObj = new List<SqlObject>();
        #endregion

        #region 报表加载初始化
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("核算项目报表过滤条件查询", this.Context.UserLocale.LCID);
            this.ReportProperty.SimpleAllCols = true;
            this.ReportProperty.IsGroupSummary = true; // 支持分组汇总


            //this.ReportProperty.DecimalControlFieldList = new List<DecimalControlField>();
            //// 标示famount用famount_decimal做精度格式化
            //this.ReportProperty.DecimalControlFieldList.Add(new DecimalControlField() { ByDecimalControlFieldName = "famount", DecimalControlFieldName = "famount_decimal" });
        }
        #endregion

        /// <summary>
        /// 报表表头数据填充,在列表中用来显示给用户当前查询条件是什么
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override Kingdee.BOS.Core.Report.ReportTitles GetReportTitles(Kingdee.BOS.Core.Report.IRptParams filter)
        {
            String  FACCTBOOKNAME, FPERIOD;
            String FSTARTYEAR, FENDYEAR, FSTARTPERIOD, FENDPERIOD;

            FACCTBOOKNAME = "";
            ReportTitles titles = new ReportTitles();
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;


            FSTARTYEAR = Convert.ToString(dyFilter["FSTARTYEAR"]);

            FENDYEAR = Convert.ToString(dyFilter["FENDYEAR"]);

            FSTARTPERIOD = Convert.ToString(dyFilter["FSTARTPERIOD"]);

            FENDPERIOD = Convert.ToString(dyFilter["FENDPERIOD"]);

            DynamicObject FACCTBOOKID = dyFilter["FACCTBOOKID"] as DynamicObject;
            if (FACCTBOOKID != null)
            {
                FACCTBOOKNAME = Convert.ToString(FACCTBOOKID["Name"]);
            }


            FPERIOD = FSTARTYEAR + '.' + FSTARTPERIOD +'-'+FENDYEAR +'.'+FENDPERIOD;
            titles.AddTitle("FACCTBOOKNAME", FACCTBOOKNAME);
            titles.AddTitle("FPERIOD", FPERIOD);
 


            return titles;
        }

        public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
        {
           
            StringBuilder sql = new StringBuilder();
            ReportHeader header = new ReportHeader();

            header.AddChild("FID", new LocaleValue("序号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FACCOUNTBOOKNAME", new LocaleValue("账簿", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCURRENCYID", new LocaleValue("币别", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FYear", new LocaleValue("年份", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FPeriod", new LocaleValue("期间", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDate", new LocaleValue("日期", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FZZNumber", new LocaleValue("组织编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FZZName", new LocaleValue("组织名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FSupNumber", new LocaleValue("供应商编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FSupName", new LocaleValue("供应商名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCustNumber", new LocaleValue("客户编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCustName", new LocaleValue("客户名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEMPNumber", new LocaleValue("员工编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEMPName", new LocaleValue("员工名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAXMNumber", new LocaleValue("一级项目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAXMName", new LocaleValue("一级项目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBXMNumber", new LocaleValue("二级项目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBXMName", new LocaleValue("二级项目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FADepNumber", new LocaleValue("一级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FADepName", new LocaleValue("一级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBDepNumber", new LocaleValue("二级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBDepName", new LocaleValue("二级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCDepNumber", new LocaleValue("三级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCDepName", new LocaleValue("三级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDDepNumber", new LocaleValue("四级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDDepName", new LocaleValue("四级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEDepNumber", new LocaleValue("五级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEDepName", new LocaleValue("五级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FFDepNumber", new LocaleValue("六级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FFDepName", new LocaleValue("六级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAcctNumber", new LocaleValue("科目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAcctName", new LocaleValue("科目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FVOUCHERGROUP", new LocaleValue("凭证字", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FVOUCHERGROUPNO", new LocaleValue("凭证号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEXPLANATION", new LocaleValue("摘要", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDeAmoumt", new LocaleValue("借方金额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FCreAmount", new LocaleValue("贷方金额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FDC", new LocaleValue("方向", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FYEAmount", new LocaleValue("余额", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);


            return header;
        
        }
        public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
        {

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            //创建所需临时表
            CreatBillDataFirstTempTable();
            T_Temp_Last = tableName;  //获得最终返回数据临时表名
            //执行建表语句
            DBUtils.ExecuteBatch(this.Context, listTableSqlObj);
            listTableSqlObj.Clear();
            //把存储过程里的数据插入临时表里
            InsertFirstTempTable(this.Context, filter);

            //最后再把数导入到最后的返回表里
            string sSQL = @"/*dialect*/ select a.* , {0}  into {1} from " + this.T_Temp_First + " a " + " order by FID";
            KSQL_SEQ = string.Format(KSQL_SEQ, "a.FID");
            sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
            DBUtils.Execute(this.Context, sSQL);
            this.DeleteTempTable();  //删除临时表


        }

        /// <summary>
        /// 创建单据查出数据时存放数据的临时表（字段只有单个业务类型的信息）
        /// </summary>
        private void CreatBillDataFirstTempTable()
        {
            StringBuilder sbSql = new StringBuilder();
            IDBService service = ServiceHelper.GetService<IDBService>();
            T_Temp_First = service.CreateTemporaryTableName(this.Context);
            sbSql.AppendFormat(@"CREATE TABLE {0}
                                (FID INT default 0,
                                FACCOUNTBOOKNAME VARCHAR(100),
                                FCURRENCYID VARCHAR(100),
                                FYear int,
                                FPeriod int,
                                FDate varchar(10),
                                FZZNumber varchar(100),
                                FZZName varchar(100),
                                FSupNumber varchar(100),
                                FSupName varchar(100),
                                FCustNumber varchar(100),
                                FCustName varchar(100),
                                FEMPNumber varchar(100),
                                FEMPName varchar(100),
                                FAXMNumber varchar(100),
                                FAXMName varchar(100),
                                FBXMNumber varchar(100),
                                FBXMName varchar(100),
                                FADepNumber varchar(100),
                                FADepName varchar(100),
                                FBDepNumber varchar(100),
                                FBDepName varchar(100),
                                FCDepNumber varchar(100),
                                FCDepName varchar(100),
                                FDDepNumber varchar(100),
                                FDDepName varchar(100),
                                FEDepNumber varchar(100),
                                FEDepName varchar(100),
                                FFDepNumber varchar(100),
                                FFDepName varchar(100),
                                FAcctNumber varchar(50),
                                FAcctName varchar(100),
                                FVOUCHERGROUP varchar(50),
                                FVOUCHERGROUPNO varchar(50),
                                FEXPLANATION varchar(500),
                                FDeAmoumt decimal (19,2),
                                FCreAmount decimal (19,2),
                                FDC VARCHAR(10),
                                FYEAmount decimal(19,2)          
                                ) ", T_Temp_First);
            listTableSqlObj.Add(new SqlObject(sbSql.ToString(), new List<SqlParam>()));
            //记录自己创建的临时表
            tempTable.Add(T_Temp_First);
            sbSql.Clear();
        }
       

        /// <summary>
        /// 根据过滤条件获得执行存储过程的参数
        /// </summary>
        private string GetSpParams(IRptParams filter)
        {

            string FSYear, FSPeroid, FEYear, FEPeroid, FCURRENCYID, FPOSTED, FFORBIDSTATUS, FACCTNames, FACCOUNTBOOKID, FACCTORGID;
            string FLEVEL, FACCTNOS, FACCTNOE, FCACCTHB;
            string FDepLV, FDepC, FDepNOS, FDepNOE;
            string FXMLV, FXMLC, FXMNOS, FXMNOE;
            string FZZNOS, FZZNOE, FSupNOS, FSupNOE;
            string FCustNOS, FCustNOE, FEMPNOS, FEMPNOE, FKMWO;
            

            FCURRENCYID = "";
            FACCOUNTBOOKID = "";
            FACCTORGID = "";
            FACCTNOS = "";
            FACCTNOE = "";
            FDepNOS = "";
            FDepNOE = "";
            FXMNOS = "";
            FXMNOE = "";
            FZZNOS = "";
            FZZNOE = "";
            FSupNOS = "";
            FSupNOE = "";
            FCustNOS = "";
            FCustNOE = "";
            FEMPNOS = "";
            FEMPNOE = "";

            ReportTitles titles = new ReportTitles();

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;

            FSYear = Convert.ToString(dyFilter["FSTARTYEAR"]);
            FEYear = Convert.ToString(dyFilter["FENDYEAR"]);
            FSPeroid = Convert.ToString(dyFilter["FSTARTPERIOD"]);
            FEPeroid = Convert.ToString(dyFilter["FENDPERIOD"]);

            DynamicObject FCURRENCY = dyFilter["F_PAEZ_CURRENCY"] as DynamicObject;
            if (FCURRENCY != null)
            {
                FCURRENCYID = Convert.ToString(FCURRENCY["ID"]);
            }

            FPOSTED = Convert.ToString(dyFilter["FNOTPOSTVOUCHER"]);
            if (FPOSTED == "True")
            { 
               FPOSTED="1";
            }
            else if (FPOSTED == "False")
            {
                FPOSTED = "0";
            }

            FFORBIDSTATUS = Convert.ToString(dyFilter["FFORBIDBALANCE"]);
            if (FFORBIDSTATUS == "True")
            {
                FFORBIDSTATUS = "1";
            }
            else if (FFORBIDSTATUS == "False")
            {
                FFORBIDSTATUS = "0";
            }

            FACCTNames = Convert.ToString(dyFilter["FSHOWFULLNAME"]);
            if (FACCTNames == "True")
            {
                FACCTNames = "1";
            }
            else if (FACCTNames == "False")
            {
                FACCTNames = "0";
            }

            FCACCTHB = Convert.ToString(dyFilter["FMULTIACCTMERGE"]);
            if (FCACCTHB == "True")
            {
                FCACCTHB = "1";
            }
            else if (FCACCTHB == "False")
            {
                FCACCTHB = "0";
            }

            FKMWO = Convert.ToString(dyFilter["F_PNVX_CheckBox"]);
            if (FKMWO == "True")
            {
                FKMWO = "1";
            }
            else if (FKMWO == "False")
            {
                FKMWO = "0";
            }


            //账簿
            DynamicObject FACCOUNTBOOK = dyFilter["FACCTBOOKID"] as DynamicObject;
            if (FACCOUNTBOOK != null)
            {
                FACCOUNTBOOKID = Convert.ToString(FACCOUNTBOOK["ID"]);
                FACCTORGID = Convert.ToString(FACCOUNTBOOK["AccountOrgID_Id"]);
            }
            //核算组织
            //DynamicObject FACCTENTITY = dyFilter["FACCTENTITY"] as DynamicObject;
            //if (FACCTENTITY != null)
            //{
                
            //}
            ////科目
            FLEVEL = Convert.ToString(dyFilter["FBALANCELEVEL"]);

            DynamicObject FSTARTBALANCE = dyFilter["FSTARTBALANCE"] as DynamicObject;
            if (FSTARTBALANCE != null)
            {
                FACCTNOS = Convert.ToString(FSTARTBALANCE["Number"]);
            }

            DynamicObject FENDBALANCE = dyFilter["FENDBALANCE"] as DynamicObject;
            if (FENDBALANCE != null)
            {
                FACCTNOE = Convert.ToString(FENDBALANCE["Number"]);
            }
            //部门 string FDepLV, FDepC, FDepNOS, FDepNOE;
            FDepLV = Convert.ToString(dyFilter["F_PAEZ_DepLV"]);
            FDepC = Convert.ToString(dyFilter["F_PAEZ_DepC"]);
            if (FDepC == "True")
            {
                FDepC = "1";
            }
            else if (FDepC == "False")
            {
                FDepC = "0";
            }
            DynamicObject F_PAEZ_DepNOS = dyFilter["F_PAEZ_DepNOS"] as DynamicObject;
            if (F_PAEZ_DepNOS != null)
            {
                FDepNOS = Convert.ToString(F_PAEZ_DepNOS["Number"]);
            }

            DynamicObject F_PAEZ_DepNOE = dyFilter["F_PAEZ_DepNOE"] as DynamicObject;
            if (F_PAEZ_DepNOE != null)
            {
                FDepNOE = Convert.ToString(F_PAEZ_DepNOE["Number"]);
            }
            //项目 FXMLV, FXMLC, FXMNOS, FXMNOE;
            FXMLV = Convert.ToString(dyFilter["F_PAEZ_XMLV"]);
            FXMLC = Convert.ToString(dyFilter["F_PAEZ_XMLC"]);
            if (FXMLC == "True")
            {
                FXMLC = "1";
            }
            else if (FXMLC == "False")
            {
                FXMLC = "0";
            }
            DynamicObject F_PAEZ_XMNOS = dyFilter["F_PAEZ_XMNOS"] as DynamicObject;
            if (F_PAEZ_XMNOS != null)
            {
                FXMNOS = Convert.ToString(F_PAEZ_XMNOS["Number"]);
            }

            DynamicObject F_PAEZ_XMNOE = dyFilter["F_PAEZ_XMNOE"] as DynamicObject;
            if (F_PAEZ_XMNOE != null)
            {
                FXMNOE = Convert.ToString(F_PAEZ_XMNOE["Number"]);
            }
            //组织 FZZNOS, FZZNOE,
            DynamicObject F_PAEZ_ZZNOS = dyFilter["F_PAEZ_ZZNOS"] as DynamicObject;
            if (F_PAEZ_ZZNOS != null)
            {
                FZZNOS = Convert.ToString(F_PAEZ_ZZNOS["Number"]);
            }

            DynamicObject F_PAEZ_ZZNOE = dyFilter["F_PAEZ_ZZNOE"] as DynamicObject;
            if (F_PAEZ_ZZNOE != null)
            {
                FZZNOE = Convert.ToString(F_PAEZ_ZZNOE["Number"]);
            }
            //供应商  FSupNOS, FSupNOE;
            DynamicObject F_PAEZ_SupNOS = dyFilter["F_PAEZ_SupNOS"] as DynamicObject;
            if (F_PAEZ_SupNOS != null)
            {
                FSupNOS = Convert.ToString(F_PAEZ_SupNOS["Number"]);
            }

            DynamicObject F_PAEZ_SupNOE = dyFilter["F_PAEZ_SupNOE"] as DynamicObject;
            if (F_PAEZ_SupNOE != null)
            {
                FSupNOE = Convert.ToString(F_PAEZ_SupNOE["Number"]);
            }
            //客户  FCustNOS, FCustNOE
            DynamicObject F_PAEZ_CustNOS = dyFilter["F_PAEZ_CustNOS"] as DynamicObject;
            if (F_PAEZ_CustNOS != null)
            {
                FCustNOS = Convert.ToString(F_PAEZ_CustNOS["Number"]);
            }

            DynamicObject F_PAEZ_CustNOE = dyFilter["F_PAEZ_CustNOE"] as DynamicObject;
            if (F_PAEZ_CustNOE != null)
            {
                FCustNOE = Convert.ToString(F_PAEZ_CustNOE["Number"]);
            }
            //员工 , FEMPNOS, FEMPNOE;
            DynamicObject F_PAEZ_EMPNOS = dyFilter["F_PAEZ_EMPNOS"] as DynamicObject;
            if (F_PAEZ_EMPNOS != null)
            {
                FEMPNOS = Convert.ToString(F_PAEZ_EMPNOS["FStaffNumber"]);
            }

            DynamicObject F_PAEZ_EMPNOE = dyFilter["F_PAEZ_EMPNOE"] as DynamicObject;
            if (F_PAEZ_EMPNOE != null)
            {
                FEMPNOE = Convert.ToString(F_PAEZ_EMPNOE["FStaffNumber"]);
            }


            return "'" + FSYear + "','" + FSPeroid + "','" + FEYear + "','" + FEPeroid + "','" + FCURRENCYID + "','" + FPOSTED + "','" + FFORBIDSTATUS + "','" + FACCTNames + "','" + FCACCTHB + "'," +
                   "'" + FACCOUNTBOOKID + "','" + FACCTORGID + "', " +
                   "'" + FLEVEL + "','" + FACCTNOS + "','" + FACCTNOE + "'," +
                   "'" + FDepLV + "','" + FDepC + "','" + FDepNOS + "','" + FDepNOE + "'," +
                   "'" + FXMLV + "','" + FXMLC + "','" + FXMNOS + "','" + FXMNOE + "'," +
                   "'" + FZZNOS + "','" + FZZNOE + "','" + FSupNOS + "','" + FSupNOE + "'," +
                   "'" + FCustNOS + "','" + FCustNOE + "','" + FEMPNOS + "','" + FEMPNOE + "','" + FKMWO + "'";
        }
        /// <summary>
        /// 填充数据到临时表里
        /// </summary>
        private void InsertFirstTempTable(Context ctx, IRptParams filter)
        {
            StringBuilder sql = new StringBuilder();
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            //这个时候还没有返回表呢,先创建一下吧 

            sql.Append("/*dialect*/ INSERT INTO " + T_Temp_First);
            sql.Append("  exec SP_HSXMBILL_SJC " + GetSpParams(filter));

            DBUtils.Execute(ctx, sql.ToString());
            sql.Clear();

        }

       

        /// <summary>
        /// 删除临时表操作
        /// </summary>
        public void DeleteTempTable()
        {
            IDBService service = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
            if (this.tempTable.Count > 0)
            {
                //删除临时表
                service.DeleteTemporaryTableName(this.Context, this.tempTable.ToArray());
                this.tempTable.Clear();
            }
        }
        /// <summary>
        /// 设置报表合计列
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        //public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        //{
        //    var result = base.GetSummaryColumnInfo(filter);
        //    result.Add(new SummaryField("FDeAmoumt", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
        //    result.Add(new SummaryField("FCreAmount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
        //    result.Add(new SummaryField("FYEAmount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
        //    return result;
        //}



    }
}
